Для анализа будут использованы следующие метрики:
По завершении анализа 2024 года, ниже будет сравнение показателей с 2023 годом.
Будут рассмотрены следующие срезы данных:
df_2024[(df_2024['order_date']>='2024-11-01') & (df_2024['order_date']<='2024-12-04')]['user_id'].nunique()
5201
Всего в Ноябрьской распродаже в оффере Aliexpress Int. в 2024 году принял участие 5201 пользователей совершивших заказы. Для сравнения в оффере Aliexpress Ru&CIS 16939 пользователей.
df_2024['traffic_type'] = df_2024['traffic_type'].map({1: 'web', 2: 'extension', 3: 'mobile'})
df_2024['traffic_type'].value_counts()
traffic_type mobile 251410 extension 183768 web 34183 Name: count, dtype: int64
Так выглядит распределение по трафику в оффере Aliexpress Int. в 2024 году. Как и в оффере Aliexpress Ru&CIS больше всего трафика идёт через мобильные, на втором месте расширение, меньше всего по вебу.
Количество заказов по каждому каналу трафика по месяцам. Чуть ниже посчитаем процентное соотношение трафика через расширение относительно общего по месяцам в 2024 году.
df_merged
| traffic_type_x | month | order_count_x | traffic_type_y | order_count_y | traffic_type | order_count | all | |
|---|---|---|---|---|---|---|---|---|
| 0 | extension | 1 | 20755 | mobile | 29695 | web | 6335 | 56785 |
| 1 | extension | 2 | 14373 | mobile | 26217 | web | 6919 | 47509 |
| 2 | extension | 3 | 19180 | mobile | 28932 | web | 5081 | 53193 |
| 3 | extension | 4 | 15586 | mobile | 20474 | web | 2488 | 38548 |
| 4 | extension | 5 | 15122 | mobile | 21085 | web | 3634 | 39841 |
| 5 | extension | 6 | 16303 | mobile | 24779 | web | 4714 | 45796 |
| 6 | extension | 7 | 15546 | mobile | 24810 | web | 1513 | 41869 |
| 7 | extension | 8 | 10896 | mobile | 12520 | web | 700 | 24116 |
| 8 | extension | 9 | 4040 | mobile | 1896 | web | 177 | 6113 |
| 9 | extension | 10 | 12148 | mobile | 11545 | web | 443 | 24136 |
| 10 | extension | 11 | 28323 | mobile | 35749 | web | 1647 | 65719 |
| 11 | extension | 12 | 11496 | mobile | 13708 | web | 532 | 25736 |
df_merged['%_extension_orders'] = df_merged['order_count_x'] / df_merged['all'] * 100
df_merged[['month', '%_extension_orders']]
| month | %_extension_orders | |
|---|---|---|
| 0 | 1 | 36.550145 |
| 1 | 2 | 30.253215 |
| 2 | 3 | 36.057376 |
| 3 | 4 | 40.432707 |
| 4 | 5 | 37.955875 |
| 5 | 6 | 35.599179 |
| 6 | 7 | 37.130096 |
| 7 | 8 | 45.181622 |
| 8 | 9 | 66.088664 |
| 9 | 10 | 50.331455 |
| 10 | 11 | 43.097126 |
| 11 | 12 | 44.668946 |
df_merged['%_extension_orders'].median()
39.194290937794534
sns.barplot(x=df_merged['month'], y=df_merged['%_extension_orders'])
plt.title('etxension traffic % out of total by month')
Text(0.5, 1.0, 'etxension traffic % out of total by month')
Сводная таблица показателей по месяцам за 2024 год. Посмотрим как менялись основные метрики на графиках.
df_grouped_2024
| month | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | %_extension_orders | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 5062 | 56785 | 646799.47 | 42300.2018 | 27804.91 | 14495.2918 | 0.744919 | 11.217898 | 11.390323 | 5.492870 | 8.356421 | 36.550145 |
| 1 | 2 | 4807 | 47509 | 537838.44 | 34459.7634 | 22533.13 | 11926.6334 | 0.725331 | 9.883295 | 11.320770 | 4.687566 | 7.168663 | 30.253215 |
| 2 | 3 | 5068 | 53193 | 679871.12 | 43584.9846 | 28127.27 | 15457.7146 | 0.819374 | 10.495856 | 12.781214 | 5.549974 | 8.600036 | 36.057376 |
| 3 | 4 | 4271 | 38548 | 452335.58 | 30001.6523 | 19387.95 | 10613.7023 | 0.778293 | 9.025521 | 11.734346 | 4.539440 | 7.024503 | 40.432707 |
| 4 | 5 | 4099 | 39841 | 486751.74 | 31877.7708 | 22355.86 | 9521.9108 | 0.800125 | 9.719688 | 12.217357 | 5.453979 | 7.776963 | 37.955875 |
| 5 | 6 | 4253 | 45796 | 609174.27 | 39205.9725 | 25306.66 | 13899.3125 | 0.856100 | 10.767929 | 13.301910 | 5.950308 | 9.218428 | 35.599179 |
| 6 | 7 | 4106 | 41869 | 490049.93 | 32381.9898 | 20807.88 | 11574.1098 | 0.773412 | 10.197029 | 11.704362 | 5.067677 | 7.886505 | 37.130096 |
| 7 | 8 | 3139 | 24116 | 287857.89 | 18570.5987 | 11962.98 | 6607.6187 | 0.770053 | 7.682701 | 11.936386 | 3.811080 | 5.916088 | 45.181622 |
| 8 | 9 | 1209 | 6113 | 103274.45 | 6251.9939 | 3952.14 | 2299.8539 | 1.022737 | 5.056245 | 16.894234 | 3.268933 | 5.171211 | 66.088664 |
| 9 | 10 | 3111 | 24136 | 288795.38 | 18966.6800 | 12312.81 | 6653.8700 | 0.785825 | 7.758277 | 11.965337 | 3.957830 | 6.096651 | 50.331455 |
| 10 | 11 | 4922 | 65719 | 859812.46 | 55472.0602 | 35796.58 | 19675.4802 | 0.844079 | 13.352093 | 13.083164 | 7.272771 | 11.270228 | 43.097126 |
| 11 | 12 | 3206 | 25736 | 326705.25 | 21388.0434 | 14273.59 | 7114.4534 | 0.831055 | 8.027449 | 12.694484 | 4.452149 | 6.671255 | 44.668946 |
data = [go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['customer_value '], name='customer_value')]
layout = go.Layout(barmode='stack', title= 'Customer value by month Aliexpress Int.', xaxis_title='month', yaxis_title='US dollars$')
fig = go.Figure(data=data, layout=layout)
fig.show()
График показывает изменение customer_value, ценность клиента, по месяцам. Как уже писал выше, эта сводная мерика показывает нам ценность одного клиента в денежном выражении, ревеню и представляет собой производную среднего ревеню с одного заказа и среднего количества заказов на одного пользователя.
df_grouped_2024['customer_value '].std()
1.6555642641805814
Посмотрим средний ревеню с одной покупки, частоту и сумму заказов, количество пользователей и уровень их кэшбека
data = [go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['avg_purchase_value'], name='avg_purchase_value')]
layout = go.Layout(barmode='stack', title= 'avg_purchase_value by month Aliexpress Int.', xaxis_title='month', yaxis_title='revenue $ per order')
fig = go.Figure(data=data, layout=layout)
fig.show()
На этом графике видим средний ревеню на один заказ по месяцам.
data = [go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['avg_purchase_frequency_rate'], name='purchase_frequency')]
layout = go.Layout(barmode='stack', title= 'purchase_frequency by month Aliexpress Int.', xaxis_title='month', yaxis_title='orders_per_user')
fig = go.Figure(data=data, layout=layout)
fig.show()
На этом графике видим среднее количество заказов на одного пользователя.
data = [go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['avg_purchase'], name='avg_purchase')]
layout = go.Layout(barmode='stack', title= 'avg_purchase by month Aliexpress Int.', xaxis_title='month', yaxis_title='US dollars$ per order')
fig = go.Figure(data=data, layout=layout)
fig.show()
На этом графике видим среднюю сумму корзины одного заказа.
data = [go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['user_count'], name='user_count')]
layout = go.Layout(barmode='stack', title= 'user_count by month Aliexpress Int.', xaxis_title='month', yaxis_title='users_count')
fig = go.Figure(data=data, layout=layout)
fig.show()
На этом графике видим количество активных пользователей по месяцам.
data = [go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['avg_user_cashback'], name='cashback USD $')]
layout = go.Layout(barmode='stack', title= 'avg_user_cashback', xaxis_title='month', yaxis_title='cashback USD $')
fig = go.Figure(data=data, layout=layout)
fig.show()
На этом графике средний кэшбек на одного пользователя по месяцам.
Зафиксируем средние значения основных метрик в месяц по году.
Важно обратить внимание, что в статистике по дням шкала метрик будет отличаться, например в Ноябре при группировке по месяцам средний customer value- 7.59 USD в Ноябре, а если просуммировать все customer value при группировке по дням и посчитать среднее за месяц получиться в среднем 3.25USD в день за месяц. Это связано с тем, что уникальными клиентами при группировке по месяцам считаются уникальные пользователи за месяц, а при группировке по дням считаются уникальные пользователи в конкретный день, то есть если в течении месяца пользователи заказывали несколько раз в разные дни, то один и тот же пользователь будет учтён несколько раз. Это не делает данные недостоверными, просто статистически другая интерпритация за период день, а не месяц.
df_grouped_nov_2024
| order_date | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | customer_value | avg_purchase | avg_user_cashback | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-11-01 | 552 | 2555 | 44383.03 | 2920.8789 | 1900.18 | 1020.6989 | 1.143201 | 4.628623 | 5.291447 | 17.371049 | 3.442355 |
| 1 | 2024-11-02 | 477 | 2038 | 26434.39 | 1715.6732 | 1132.20 | 583.4732 | 0.841842 | 4.272537 | 3.596799 | 12.970751 | 2.373585 |
| 2 | 2024-11-03 | 476 | 1837 | 19139.68 | 1253.9784 | 811.31 | 442.6684 | 0.682623 | 3.859244 | 2.634408 | 10.418987 | 1.704433 |
| 3 | 2024-11-04 | 452 | 1674 | 20800.21 | 1340.5757 | 870.71 | 469.8657 | 0.800822 | 3.703540 | 2.965875 | 12.425454 | 1.926350 |
| 4 | 2024-11-05 | 458 | 1940 | 20747.72 | 1371.3541 | 892.01 | 479.3441 | 0.706884 | 4.235808 | 2.994223 | 10.694701 | 1.947620 |
| 5 | 2024-11-06 | 488 | 1888 | 24879.45 | 1619.1201 | 1066.17 | 552.9501 | 0.857585 | 3.868852 | 3.317869 | 13.177675 | 2.184775 |
| 6 | 2024-11-07 | 517 | 2127 | 25003.49 | 1650.9630 | 1063.11 | 587.8530 | 0.776193 | 4.114120 | 3.193352 | 11.755284 | 2.056306 |
| 7 | 2024-11-08 | 382 | 1643 | 20054.38 | 1282.2445 | 838.66 | 443.5845 | 0.780429 | 4.301047 | 3.356661 | 12.205953 | 2.195445 |
| 8 | 2024-11-09 | 280 | 989 | 9046.71 | 620.7892 | 411.37 | 209.4192 | 0.627694 | 3.532143 | 2.217104 | 9.147331 | 1.469179 |
| 9 | 2024-11-10 | 268 | 738 | 7327.60 | 487.9713 | 308.79 | 179.1813 | 0.661208 | 2.753731 | 1.820788 | 9.928997 | 1.152201 |
| 10 | 2024-11-11 | 1162 | 5740 | 88409.43 | 5356.6748 | 3449.69 | 1906.9848 | 0.933219 | 4.939759 | 4.609875 | 15.402340 | 2.968752 |
| 11 | 2024-11-12 | 855 | 3725 | 53302.79 | 3268.1107 | 2083.10 | 1185.0107 | 0.877345 | 4.356725 | 3.822352 | 14.309474 | 2.436374 |
| 12 | 2024-11-13 | 678 | 2825 | 37134.13 | 2438.8557 | 1571.40 | 867.4557 | 0.863312 | 4.166667 | 3.597132 | 13.144825 | 2.317699 |
| 13 | 2024-11-14 | 663 | 2604 | 38446.39 | 2461.1553 | 1601.82 | 859.3353 | 0.945144 | 3.927602 | 3.712150 | 14.764359 | 2.416018 |
| 14 | 2024-11-15 | 620 | 2536 | 33419.94 | 2090.6588 | 1345.44 | 745.2188 | 0.824392 | 4.090323 | 3.372030 | 13.178210 | 2.170065 |
| 15 | 2024-11-16 | 640 | 2520 | 31000.22 | 2025.3883 | 1302.96 | 722.4283 | 0.803726 | 3.937500 | 3.164669 | 12.301675 | 2.035875 |
| 16 | 2024-11-17 | 613 | 2473 | 25174.95 | 1652.2856 | 1073.57 | 578.7156 | 0.668130 | 4.034258 | 2.695409 | 10.179923 | 1.751338 |
| 17 | 2024-11-18 | 734 | 3311 | 41313.79 | 2688.8351 | 1734.00 | 954.8351 | 0.812092 | 4.510899 | 3.663263 | 12.477738 | 2.362398 |
| 18 | 2024-11-19 | 547 | 2252 | 30807.00 | 2065.1926 | 1330.56 | 734.6326 | 0.917048 | 4.117002 | 3.775489 | 13.679840 | 2.432468 |
| 19 | 2024-11-20 | 281 | 888 | 9257.74 | 646.2642 | 416.18 | 230.0842 | 0.727775 | 3.160142 | 2.299873 | 10.425383 | 1.481068 |
| 20 | 2024-11-21 | 266 | 804 | 8140.11 | 556.6090 | 349.56 | 207.0490 | 0.692300 | 3.022556 | 2.092515 | 10.124515 | 1.314135 |
| 21 | 2024-11-22 | 720 | 2931 | 42776.81 | 2797.2064 | 1763.57 | 1033.6364 | 0.954352 | 4.070833 | 3.885009 | 14.594613 | 2.449403 |
| 22 | 2024-11-23 | 578 | 2138 | 22354.01 | 1520.0412 | 975.00 | 545.0412 | 0.710964 | 3.698962 | 2.629829 | 10.455571 | 1.686851 |
| 23 | 2024-11-24 | 521 | 2033 | 22714.85 | 1468.9637 | 950.26 | 518.7037 | 0.722560 | 3.902111 | 2.819508 | 11.173069 | 1.823916 |
| 24 | 2024-11-25 | 540 | 2026 | 24654.91 | 1598.3096 | 1035.10 | 563.2096 | 0.788899 | 3.751852 | 2.959833 | 12.169255 | 1.916852 |
| 25 | 2024-11-26 | 518 | 1840 | 27770.22 | 1774.2354 | 1137.15 | 637.0854 | 0.964258 | 3.552124 | 3.425165 | 15.092511 | 2.195270 |
| 26 | 2024-11-27 | 491 | 1656 | 25286.27 | 1527.8300 | 990.13 | 537.7000 | 0.922603 | 3.372709 | 3.111670 | 15.269487 | 2.016558 |
| 27 | 2024-11-28 | 501 | 1827 | 22452.71 | 1502.7149 | 971.26 | 531.4549 | 0.822504 | 3.646707 | 2.999431 | 12.289387 | 1.938643 |
| 28 | 2024-11-29 | 555 | 2062 | 27208.57 | 1799.6940 | 1140.63 | 659.0640 | 0.872790 | 3.715315 | 3.242692 | 13.195233 | 2.055189 |
| 29 | 2024-11-30 | 517 | 2099 | 30370.96 | 1969.4865 | 1280.69 | 688.7965 | 0.938298 | 4.059961 | 3.809452 | 14.469252 | 2.477157 |
| 30 | 2024-12-01 | 523 | 2036 | 23161.96 | 1499.3510 | 964.81 | 534.5410 | 0.736420 | 3.892925 | 2.866828 | 11.376208 | 1.844761 |
| 31 | 2024-12-02 | 580 | 2218 | 29815.54 | 1908.8587 | 1228.91 | 679.9487 | 0.860622 | 3.824138 | 3.291136 | 13.442534 | 2.118810 |
| 32 | 2024-12-03 | 635 | 2779 | 38241.09 | 2510.3645 | 1625.04 | 885.3245 | 0.903334 | 4.376378 | 3.953330 | 13.760738 | 2.559118 |
| 33 | 2024-12-04 | 537 | 1988 | 26738.17 | 1786.3506 | 1157.66 | 628.6906 | 0.898567 | 3.702048 | 3.326537 | 13.449784 | 2.155791 |
data = [go.Scatter(x=df_grouped_nov_2024['order_date'], y=df_grouped_nov_2024['customer_value '], name='customer_value')]
layout = go.Layout(barmode='stack', title= 'Customer value by day Aliexpress Int.', xaxis_title='day', yaxis_title='US dollars$')
fig = go.Figure(data=data, layout=layout)
fig.show()
data = [go.Scatter(x=df_grouped_nov_2024['order_date'], y=df_grouped_nov_2024['user_count'], name='user_count')]
layout = go.Layout(barmode='stack', title= 'user_count by day', xaxis_title='day', yaxis_title='users_count')
fig = go.Figure(data=data, layout=layout)
fig.show()
Наши метрики по дням по время Ноябрьской распродажи Aliexpress Int.
df_grouped_nov_2024['customer_value '].mean()
3.2504030610403056
df_grouped_nov_2024['avg_purchase_value'].mean()
0.8246803401108906
df_grouped_nov_2024['avg_purchase_frequency_rate'].mean()
3.9146806295207623
df_grouped_nov_2024['avg_purchase'].mean()
12.671238307172448
df_grouped_nov_2024['avg_user_cashback'].mean()
2.0993163666110415
df_grouped_country_2024[['country', 'user_count', 'revenue_sum', 'avg_purchase_value', 'avg_purchase_frequency_rate','avg_purchase', 'avg_user_cashback', 'customer_value' ]].sort_values(by='revenue_sum', ascending=False).head(10).reset_index()
| index | country | user_count | revenue_sum | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 130 | UA | 2668 | 80586.2569 | 1.019021 | 29.640930 | 15.672755 | 20.033205 | 30.204744 |
| 1 | 139 | VN | 90 | 24945.1800 | 0.776963 | 356.733333 | 11.061949 | 186.647222 | 277.168667 |
| 2 | 21 | BR | 203 | 24345.2100 | 1.050449 | 114.167488 | 15.032255 | 76.946847 | 119.927143 |
| 3 | 58 | IL | 766 | 22895.4272 | 0.691496 | 43.224543 | 10.284111 | 19.390144 | 29.889592 |
| 4 | 17 | BG | 641 | 15360.4500 | 0.780630 | 30.697348 | 11.726527 | 15.552637 | 23.963261 |
| 5 | 76 | LT | 581 | 11942.1800 | 0.779465 | 26.370052 | 11.525819 | 13.282031 | 20.554527 |
| 6 | 78 | LV | 688 | 9720.7350 | 0.579754 | 24.370640 | 8.893707 | 9.016701 | 14.128975 |
| 7 | 90 | MX | 209 | 9421.6220 | 1.110647 | 40.588517 | 16.920905 | 29.321531 | 45.079531 |
| 8 | 133 | US | 310 | 7329.1798 | 0.539505 | 43.822581 | 8.503602 | 14.890000 | 23.642515 |
| 9 | 40 | ES | 462 | 7032.5100 | 0.498937 | 30.508658 | 7.788748 | 9.980866 | 15.221883 |
В таблице топ 10 стран по ревеню в 2024 году.
Отдельно посмотрим GMV, ревеню, количество заказов и пользователей по месяцам по Вьетнаму, Бразилии и Мексике.
df_2024[df_2024['country']=='VN'].groupby('month').agg(GMV=('GMV', 'sum'), Revenue=('Revenue', 'sum'), order_count = ('order_number', 'nunique'), user_count=('user_id', 'nunique'))
| GMV | Revenue | order_count | user_count | |
|---|---|---|---|---|
| month | ||||
| 1 | 11537.38 | 799.96 | 725 | 37 |
| 2 | 19542.08 | 1344.95 | 1399 | 44 |
| 3 | 46823.60 | 3250.32 | 4041 | 48 |
| 4 | 40789.27 | 2800.18 | 3671 | 45 |
| 5 | 42157.97 | 2875.77 | 3754 | 49 |
| 6 | 31118.48 | 2156.20 | 2791 | 41 |
| 7 | 22137.21 | 1539.01 | 2312 | 43 |
| 8 | 28300.72 | 1980.58 | 2617 | 48 |
| 9 | 19404.55 | 1404.80 | 1715 | 39 |
| 10 | 31568.10 | 2243.58 | 2881 | 51 |
| 11 | 40468.87 | 3020.31 | 4031 | 47 |
| 12 | 21306.70 | 1529.52 | 2169 | 41 |
df_2024[df_2024['country']=='BR'].groupby('month').agg(GMV=('GMV', 'sum'), Revenue=('Revenue', 'sum'), order_count = ('order_number', 'nunique'), user_count=('user_id', 'nunique'))
| GMV | Revenue | order_count | user_count | |
|---|---|---|---|---|
| month | ||||
| 1 | 18566.20 | 1275.27 | 620 | 38 |
| 2 | 50657.92 | 3355.00 | 2064 | 68 |
| 3 | 41193.87 | 3000.73 | 2695 | 78 |
| 4 | 29356.47 | 2145.50 | 2136 | 68 |
| 5 | 31111.35 | 2283.36 | 2141 | 63 |
| 6 | 64872.00 | 4416.14 | 4599 | 77 |
| 7 | 41209.72 | 3029.93 | 3855 | 92 |
| 8 | 16033.43 | 1145.61 | 1847 | 55 |
| 9 | 3493.36 | 239.59 | 259 | 24 |
| 10 | 16316.47 | 1100.16 | 1177 | 60 |
| 11 | 25178.00 | 1647.60 | 1422 | 70 |
| 12 | 10398.76 | 706.32 | 361 | 51 |
df_2024[df_2024['country']=='MX'].groupby('month').agg(GMV=('GMV', 'sum'), Revenue=('Revenue', 'sum'), order_count = ('order_number', 'nunique'), user_count=('user_id', 'nunique'))
| GMV | Revenue | order_count | user_count | |
|---|---|---|---|---|
| month | ||||
| 1 | 4234.25 | 278.790 | 230 | 34 |
| 2 | 15755.18 | 1082.850 | 961 | 70 |
| 3 | 22571.98 | 1430.300 | 1291 | 73 |
| 4 | 11438.97 | 699.220 | 618 | 63 |
| 5 | 18331.41 | 1120.870 | 818 | 75 |
| 6 | 19783.22 | 1305.230 | 1103 | 83 |
| 7 | 9085.02 | 614.360 | 782 | 74 |
| 8 | 7919.79 | 546.340 | 613 | 67 |
| 9 | 1057.24 | 75.260 | 54 | 16 |
| 10 | 3630.69 | 251.240 | 344 | 44 |
| 11 | 19568.04 | 1316.872 | 1126 | 74 |
| 12 | 10164.25 | 700.290 | 543 | 50 |
По GMV, Revenue и количеству заказов прослеживаются четкие пики по месяцам.
Теперь сгруппируем данные по странам отдельно по Ноябрю
df_grouped_country_nov_2024[['country', 'user_count', 'revenue_sum', 'avg_purchase_value', 'avg_purchase_frequency_rate','avg_purchase', 'avg_user_cashback', 'customer_value' ]].sort_values(by='revenue_sum', ascending=False).head(10).reset_index()
| index | country | user_count | revenue_sum | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 106 | UA | 1207 | 13531.0246 | 1.088490 | 10.299089 | 16.826017 | 7.335551 | 11.210459 |
| 1 | 48 | IL | 413 | 3823.4872 | 0.672912 | 13.757869 | 9.990111 | 5.851719 | 9.257838 |
| 2 | 111 | VN | 48 | 3446.2400 | 0.725830 | 98.916667 | 9.730666 | 45.886250 | 71.796667 |
| 3 | 12 | BG | 307 | 2405.7300 | 0.845302 | 9.270358 | 12.289803 | 4.994919 | 7.836254 |
| 4 | 62 | LT | 280 | 2405.6900 | 1.007408 | 8.528571 | 15.025791 | 5.522107 | 8.591750 |
| 5 | 16 | BR | 75 | 1816.0500 | 1.161900 | 20.840000 | 17.790774 | 16.010533 | 24.214000 |
| 6 | 74 | MX | 84 | 1803.2320 | 1.238484 | 17.333333 | 18.254258 | 14.167262 | 21.467048 |
| 7 | 108 | US | 95 | 1425.6800 | 0.687075 | 21.842105 | 10.606761 | 8.814737 | 15.007158 |
| 8 | 64 | LV | 286 | 1286.2100 | 0.560929 | 8.017483 | 8.600375 | 2.794196 | 4.497238 |
| 9 | 87 | PS | 20 | 1175.1200 | 0.942358 | 62.350000 | 13.854747 | 40.016500 | 58.756000 |
По итогу анализа метрик по странам, Вьетнам, Бразилия и Мексика представляют из себе весьма интересные гео, для дальнейшей работы с ними и изучения
Сначала посмотрим статистику по уровням за весь год, затем отдельно по Ноябрю в период распродаж
df_grouped_level_2024 = df_2024.groupby(['level']).agg(user_count = ('user_id', 'nunique'), order_count = ('order_number', 'nunique'),
GMV_sum = ('GMV', 'sum'), revenue_sum = ('Revenue', 'sum'),
user_com_sum = ('users_comission', 'sum')).reset_index()
df_grouped_level_2024
| level | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 1345 | 7049 | 87792.12 | 5440.5807 | 2711.21 | 2729.3707 | 0.771823 | 5.240892 | 12.454550 | 2.015770 | 4.045041 |
| 1 | 2 | 419 | 3314 | 27080.26 | 1548.5895 | 783.38 | 765.2095 | 0.467287 | 7.909308 | 8.171473 | 1.869642 | 3.695918 |
| 2 | 3 | 678 | 6690 | 55158.19 | 3353.7778 | 1763.23 | 1590.5478 | 0.501312 | 9.867257 | 8.244871 | 2.600634 | 4.946575 |
| 3 | 4 | 1588 | 22552 | 185508.43 | 11454.7221 | 6390.56 | 5064.1621 | 0.507925 | 14.201511 | 8.225808 | 4.024282 | 7.213301 |
| 4 | 5 | 4038 | 119035 | 1034312.94 | 66406.6345 | 40690.59 | 25716.0445 | 0.557875 | 29.478702 | 8.689150 | 10.076917 | 16.445427 |
| 5 | 6 | 2248 | 174286 | 2422365.97 | 155845.9034 | 102729.98 | 53115.9234 | 0.894196 | 77.529359 | 13.898798 | 45.698390 | 69.326469 |
| 6 | 7 | 1150 | 136435 | 1957048.07 | 130411.5034 | 89552.81 | 40858.6934 | 0.955851 | 118.639130 | 14.344179 | 77.872009 | 113.401307 |
Посмотрим customer value по уровням на графике.
data = [go.Scatter(x=df_grouped_level_2024['level'], y=df_grouped_level_2024['customer_value'], name='customer_value')]
layout = go.Layout(barmode='stack', title= 'Customer value by lvl Aliexpress Int.', xaxis_title='level', yaxis_title='US dollars$')
fig = go.Figure(data=data, layout=layout)
fig.show()
Группировка по уровню Ноябрь
df_grouped_level_nov_2024
| level | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 448 | 2956 | 40851.36 | 2618.8825 | 1287.91 | 1330.9725 | 0.885955 | 6.598214 | 5.845720 |
| 1 | 2 | 89 | 791 | 8315.30 | 480.7561 | 248.41 | 232.3461 | 0.607783 | 8.887640 | 5.401754 |
| 2 | 3 | 158 | 1284 | 12687.82 | 784.7429 | 438.86 | 345.8829 | 0.611170 | 8.126582 | 4.966727 |
| 3 | 4 | 520 | 3591 | 33103.82 | 1994.4176 | 1149.77 | 844.6476 | 0.555393 | 6.905769 | 3.835418 |
| 4 | 5 | 1976 | 21219 | 202172.89 | 12971.9967 | 7971.74 | 5000.2567 | 0.611339 | 10.738360 | 6.564776 |
| 5 | 6 | 1425 | 28191 | 406462.65 | 25925.5178 | 17003.05 | 8922.4678 | 0.919638 | 19.783158 | 18.193346 |
| 6 | 7 | 585 | 16708 | 274175.38 | 18400.6714 | 12673.26 | 5727.4114 | 1.101309 | 28.560684 | 31.454139 |
df_2024_all['order_number'].nunique() / df_2024_all['activations'].nunique()
0.29913421430824777
Средняя конверсия за весь 2024 год по офферу Aliexpress Int. составила 29.9%
df_conversion_2024 = df_2024_all.groupby(['month']).agg(order_count=('order_number', 'nunique'), activation_count=('activations', 'nunique')).reset_index()
df_conversion_2024['%_conversion'] = df_conversion_2024['order_count'] / df_conversion_2024['activation_count'] * 100
df_conversion_2024
| month | order_count | activation_count | %_conversion | |
|---|---|---|---|---|
| 0 | 1 | 57085 | 128557 | 44.404428 |
| 1 | 2 | 47485 | 108871 | 43.615839 |
| 2 | 3 | 53116 | 125001 | 42.492460 |
| 3 | 4 | 38582 | 111153 | 34.710714 |
| 4 | 5 | 40990 | 98825 | 41.477359 |
| 5 | 6 | 44637 | 113511 | 39.323942 |
| 6 | 7 | 41952 | 83074 | 50.499555 |
| 7 | 8 | 23858 | 156818 | 15.213815 |
| 8 | 9 | 6145 | 194312 | 3.162440 |
| 9 | 10 | 24552 | 173282 | 14.168812 |
| 10 | 11 | 65557 | 192488 | 34.057707 |
| 11 | 12 | 25411 | 83203 | 30.540966 |
sns.barplot(x=df_conversion_2024['month'], y=df_conversion_2024['%_conversion'])
plt.title('%_Conversion')
Text(0.5, 1.0, '%_Conversion')
df_conversion_2024[df_conversion_2024['month'] != 9]['%_conversion'].mean()
35.50050875002477
Посмотрим отдельно конверсию по дням, в дни Ноябрьской распродажи с 2024-11-01 по 2024-12-04
df_conversion_nov_2024 = df_2024_all[(df_2024_all['activation_date']>='2024-11-01') & (df_2024_all['activation_date']<='2024-12-04')].groupby(['activation_date']).agg(order_count=('order_number', 'nunique'), activation_count=('activations', 'nunique')).reset_index()
df_conversion_nov_2024['%_conversion'] = df_conversion_nov_2024['order_count'] / df_conversion_nov_2024['activation_count'] * 100
df_conversion_nov_2024
| activation_date | order_count | activation_count | %_conversion | |
|---|---|---|---|---|
| 0 | 2024-11-01 | 2525 | 6562 | 38.479122 |
| 1 | 2024-11-02 | 1966 | 5690 | 34.551845 |
| 2 | 2024-11-03 | 1847 | 5629 | 32.812222 |
| 3 | 2024-11-04 | 1765 | 6094 | 28.962914 |
| 4 | 2024-11-05 | 1894 | 5750 | 32.939130 |
| 5 | 2024-11-06 | 1869 | 6013 | 31.082654 |
| 6 | 2024-11-07 | 2168 | 5696 | 38.061798 |
| 7 | 2024-11-08 | 1754 | 5368 | 32.675112 |
| 8 | 2024-11-09 | 1274 | 4822 | 26.420572 |
| 9 | 2024-11-10 | 1312 | 5110 | 25.675147 |
| 10 | 2024-11-11 | 5739 | 11779 | 48.722302 |
| 11 | 2024-11-12 | 3161 | 9216 | 34.299045 |
| 12 | 2024-11-13 | 2627 | 7463 | 35.200322 |
| 13 | 2024-11-14 | 2514 | 7362 | 34.148329 |
| 14 | 2024-11-15 | 2432 | 6848 | 35.514019 |
| 15 | 2024-11-16 | 2495 | 6786 | 36.766873 |
| 16 | 2024-11-17 | 2602 | 6413 | 40.573834 |
| 17 | 2024-11-18 | 3294 | 7549 | 43.634919 |
| 18 | 2024-11-19 | 2267 | 6374 | 35.566363 |
| 19 | 2024-11-20 | 1069 | 5300 | 20.169811 |
| 20 | 2024-11-21 | 1246 | 5093 | 24.464952 |
| 21 | 2024-11-22 | 2523 | 7529 | 33.510426 |
| 22 | 2024-11-23 | 1906 | 5748 | 33.159360 |
| 23 | 2024-11-24 | 2027 | 5986 | 33.862345 |
| 24 | 2024-11-25 | 1883 | 6284 | 29.964990 |
| 25 | 2024-11-26 | 1751 | 5910 | 29.627750 |
| 26 | 2024-11-27 | 1712 | 6633 | 25.810342 |
| 27 | 2024-11-28 | 1811 | 5917 | 30.606726 |
| 28 | 2024-11-29 | 2041 | 5935 | 34.389217 |
| 29 | 2024-11-30 | 2083 | 5629 | 37.004797 |
| 30 | 2024-12-01 | 2071 | 5491 | 37.716263 |
| 31 | 2024-12-02 | 2330 | 6151 | 37.880020 |
| 32 | 2024-12-03 | 2721 | 6455 | 42.153369 |
| 33 | 2024-12-04 | 1879 | 5904 | 31.825881 |
data = [go.Scatter(x=df_conversion_nov_2024['activation_date'], y=df_conversion_nov_2024['%_conversion'], name='conversion')]
layout = go.Layout(barmode='stack', title= '%_conversion_Nov', xaxis_title='days', yaxis_title='%')
fig = go.Figure(data=data, layout=layout)
fig.show()
df_conversion_nov_2024['%_conversion'].mean()
33.7715521557841
df_2024_all[(df_2024_all['reg_date'] >= '2024-11-01') & (df_2024_all['reg_date'] <= '2024-12-04')]['user_id'].nunique()
873
873 / 5201 * 100
16.78523360892136
Во время Ноябрьской распродажи принял участие 873 новых пользователя, новые пользователи составили 16.7% от общего числа пользователей совершивших заказы. В Aliexpress RU&CIS этот показатель 9.2%.
df_new_users_nov_2024
| order_date | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-11-01 | 2 | 6 | 79.59 | 3.8100 | 1.76 | 2.0500 | 0.635000 | 3.000000 | 13.265000 | 0.880000 | 1.905000 |
| 1 | 2024-11-02 | 1 | 3 | 1.73 | 0.1100 | 0.06 | 0.0500 | 0.036667 | 3.000000 | 0.576667 | 0.060000 | 0.110000 |
| 2 | 2024-11-03 | 3 | 6 | 94.10 | 6.5600 | 3.28 | 3.2800 | 1.093333 | 2.000000 | 15.683333 | 1.093333 | 2.186667 |
| 3 | 2024-11-04 | 6 | 15 | 131.04 | 9.0900 | 4.19 | 4.9000 | 0.606000 | 2.500000 | 8.736000 | 0.698333 | 1.515000 |
| 4 | 2024-11-05 | 1 | 1 | 13.51 | 0.9400 | 0.50 | 0.4400 | 0.940000 | 1.000000 | 13.510000 | 0.500000 | 0.940000 |
| 5 | 2024-11-06 | 2 | 8 | 182.33 | 12.3600 | 5.93 | 6.4300 | 1.545000 | 4.000000 | 22.791250 | 2.965000 | 6.180000 |
| 6 | 2024-11-07 | 1 | 1 | 0.04 | 0.0100 | 0.01 | 0.0000 | 0.010000 | 1.000000 | 0.040000 | 0.010000 | 0.010000 |
| 7 | 2024-11-08 | 4 | 11 | 119.86 | 8.3300 | 3.97 | 4.3600 | 0.757273 | 2.750000 | 10.896364 | 0.992500 | 2.082500 |
| 8 | 2024-11-09 | 2 | 7 | 38.41 | 2.5800 | 1.18 | 1.4000 | 0.368571 | 3.500000 | 5.487143 | 0.590000 | 1.290000 |
| 9 | 2024-11-10 | 4 | 4 | 22.51 | 1.5100 | 0.71 | 0.8000 | 0.377500 | 1.000000 | 5.627500 | 0.177500 | 0.377500 |
| 10 | 2024-11-11 | 15 | 42 | 2288.87 | 88.7662 | 48.20 | 40.5662 | 2.113481 | 2.800000 | 54.496905 | 3.213333 | 5.917747 |
| 11 | 2024-11-12 | 9 | 37 | 584.69 | 35.3023 | 16.46 | 18.8423 | 0.954116 | 4.111111 | 15.802432 | 1.828889 | 3.922478 |
| 12 | 2024-11-13 | 10 | 27 | 465.85 | 32.1900 | 17.30 | 14.8900 | 1.192222 | 2.700000 | 17.253704 | 1.730000 | 3.219000 |
| 13 | 2024-11-14 | 8 | 30 | 610.18 | 38.8607 | 19.03 | 19.8307 | 1.295357 | 3.750000 | 20.339333 | 2.378750 | 4.857588 |
| 14 | 2024-11-15 | 9 | 28 | 1271.46 | 19.8918 | 9.97 | 9.9218 | 0.710421 | 3.111111 | 45.409286 | 1.107778 | 2.210200 |
| 15 | 2024-11-16 | 4 | 23 | 442.66 | 30.8300 | 14.73 | 16.1000 | 1.340435 | 5.750000 | 19.246087 | 3.682500 | 7.707500 |
| 16 | 2024-11-17 | 3 | 9 | 71.98 | 5.0000 | 2.55 | 2.4500 | 0.555556 | 3.000000 | 7.997778 | 0.850000 | 1.666667 |
| 17 | 2024-11-18 | 11 | 46 | 1508.19 | 71.4600 | 34.14 | 37.3200 | 1.553478 | 4.181818 | 32.786739 | 3.103636 | 6.496364 |
| 18 | 2024-11-19 | 5 | 19 | 214.09 | 15.7300 | 7.69 | 8.0400 | 0.827895 | 3.800000 | 11.267895 | 1.538000 | 3.146000 |
| 19 | 2024-11-20 | 5 | 10 | 62.44 | 4.3200 | 2.19 | 2.1300 | 0.432000 | 2.000000 | 6.244000 | 0.438000 | 0.864000 |
| 20 | 2024-11-21 | 3 | 3 | 13.19 | 0.9100 | 0.41 | 0.5000 | 0.303333 | 1.000000 | 4.396667 | 0.136667 | 0.303333 |
| 21 | 2024-11-22 | 10 | 51 | 535.31 | 36.3700 | 17.22 | 19.1500 | 0.713137 | 5.100000 | 10.496275 | 1.722000 | 3.637000 |
| 22 | 2024-11-23 | 10 | 51 | 386.61 | 26.4400 | 12.52 | 13.9200 | 0.518431 | 5.100000 | 7.580588 | 1.252000 | 2.644000 |
| 23 | 2024-11-24 | 10 | 26 | 241.09 | 15.0500 | 7.69 | 7.3600 | 0.578846 | 2.600000 | 9.272692 | 0.769000 | 1.505000 |
| 24 | 2024-11-25 | 7 | 22 | 236.93 | 16.2200 | 7.97 | 8.2500 | 0.737273 | 3.142857 | 10.769545 | 1.138571 | 2.317143 |
| 25 | 2024-11-26 | 7 | 29 | 442.88 | 24.7900 | 11.85 | 12.9400 | 0.854828 | 4.142857 | 15.271724 | 1.692857 | 3.541429 |
| 26 | 2024-11-27 | 9 | 24 | 511.80 | 35.3700 | 18.81 | 16.5600 | 1.473750 | 2.666667 | 21.325000 | 2.090000 | 3.930000 |
| 27 | 2024-11-28 | 4 | 25 | 369.03 | 25.0500 | 12.66 | 12.3900 | 1.002000 | 6.250000 | 14.761200 | 3.165000 | 6.262500 |
| 28 | 2024-11-29 | 7 | 19 | 769.52 | 53.6300 | 25.97 | 27.6600 | 2.822632 | 2.714286 | 40.501053 | 3.710000 | 7.661429 |
| 29 | 2024-11-30 | 8 | 26 | 168.01 | 11.8000 | 5.53 | 6.2700 | 0.453846 | 3.250000 | 6.461923 | 0.691250 | 1.475000 |
| 30 | 2024-12-01 | 10 | 20 | 108.28 | 7.5100 | 3.72 | 3.7900 | 0.375500 | 2.000000 | 5.414000 | 0.372000 | 0.751000 |
| 31 | 2024-12-02 | 8 | 15 | 1168.67 | 67.1900 | 35.34 | 31.8500 | 4.479333 | 1.875000 | 77.911333 | 4.417500 | 8.398750 |
| 32 | 2024-12-03 | 5 | 23 | 145.22 | 10.0300 | 4.95 | 5.0800 | 0.436087 | 4.600000 | 6.313913 | 0.990000 | 2.006000 |
| 33 | 2024-12-04 | 9 | 33 | 312.40 | 24.7400 | 10.99 | 13.7500 | 0.749697 | 3.666667 | 9.466667 | 1.221111 | 2.748889 |
df_new_users_nov_2024['avg_purchase_value'].mean()
0.9659705411245065
df_new_users_nov_2024['avg_purchase_frequency_rate'].mean()
3.1488933452168744
df_new_users_nov_2024['customer_value'].mean()
3.05252003342246
В отличии от оффера Aliexpress RU&CIS, здесь мы не видим превосходящих метрик по новым пользователям относительно метрик по пользователям в целом
df_2024[df_2024['cashback_link_count'] >= 1]['user_id'].drop_duplicates()
1 2584994
12 3100571
17 840196
18 2444755
24 843295
...
459657 1508985
462375 3031548
463409 298967
468286 1307418
468701 1243889
Name: user_id, Length: 2970, dtype: int64
df_2024[df_2024['cashback_link_count'] >= 50]['user_id'].drop_duplicates()
17 840196
414 25794
662 856755
766 2652988
826 2444191
...
412928 2786363
415143 2364298
417981 611529
420320 2875573
420377 2476058
Name: user_id, Length: 167, dtype: int64
Всего создателей кэшбек ссылок на оффере Aliexpress Int в 2024 году- 2970 пользователей, из них 167 пользователей создали боее 50 кэшбек ссылок за год.
df_2024[(df_2024['cashback_link_count'].notna()) & (df_2024['order_date']>= '2024-11-01')
& (df_2024['order_date']<= '2024-12-04')]['user_id'].nunique()
1539
1539 / 2970 * 100
51.81818181818182
df_grouped_cb_link
| month | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | |
|---|---|---|---|---|---|---|
| 0 | 1 | 1631 | 22742 | 263414.97 | 16847.2035 | 11222.44 |
| 1 | 2 | 1487 | 17515 | 213214.91 | 13097.1802 | 8743.79 |
| 2 | 3 | 1569 | 21231 | 293722.47 | 18028.2521 | 11925.85 |
| 3 | 4 | 1326 | 15261 | 186305.53 | 12003.6766 | 7987.00 |
| 4 | 5 | 1288 | 16070 | 191307.36 | 12372.0372 | 8913.93 |
| 5 | 6 | 1378 | 18827 | 251265.30 | 16057.6140 | 10594.55 |
| 6 | 7 | 1353 | 16496 | 200545.87 | 13133.5880 | 8618.58 |
| 7 | 8 | 1016 | 9097 | 106585.57 | 6918.8783 | 4488.88 |
| 8 | 9 | 397 | 2197 | 39556.22 | 2289.5742 | 1474.28 |
| 9 | 10 | 978 | 7828 | 99965.95 | 6605.0102 | 4330.61 |
| 10 | 11 | 1483 | 22268 | 325042.92 | 20978.9063 | 13742.89 |
| 11 | 12 | 994 | 8654 | 114603.43 | 7405.3981 | 5027.87 |
df_grouped_cb_link['revenue_sum'].sum() / df_grouped_2024['revenue_sum'].sum() * 100
38.91915094740444
По ревеню создатели кб ссылок в общем составили 38.9% от общего ревеню в 2024 году по офферу Aliexpress Int.
df_reactivated[(df_reactivated['date']>='2024-11-01') & (df_reactivated['date']<='2024-12-04')]['user_id'].nunique()
3623
df_2024_nov = df_2024[(df_2024['order_date']>='2024-11-01') & (df_2024['order_date']<='2024-12-04')]
df_2024_nov[df_2024_nov['user_id'].isin(df_reactivated['user_id'])]['user_id'].nunique()
671
671 / 3623 *100
18.520563069279603
Посмотрим активность реактивированных по тем же метрикам в течении периода распродаж.
df_reactivated_grouped
| order_date | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-11-01 | 45 | 185 | 2285.36 | 148.9488 | 92.03 | 56.9188 | 0.805129 | 4.111111 | 12.353297 | 2.045111 | 3.309973 |
| 1 | 2024-11-02 | 48 | 191 | 2863.60 | 201.2288 | 129.71 | 71.5188 | 1.053554 | 3.979167 | 14.992670 | 2.702292 | 4.192267 |
| 2 | 2024-11-03 | 37 | 155 | 1358.12 | 77.7438 | 48.95 | 28.7938 | 0.501573 | 4.189189 | 8.762065 | 1.322973 | 2.101184 |
| 3 | 2024-11-04 | 23 | 66 | 2123.67 | 156.5800 | 100.80 | 55.7800 | 2.372424 | 2.869565 | 32.176818 | 4.382609 | 6.807826 |
| 4 | 2024-11-05 | 49 | 169 | 2147.15 | 143.3129 | 99.82 | 43.4929 | 0.848005 | 3.448980 | 12.705030 | 2.037143 | 2.924753 |
| 5 | 2024-11-06 | 44 | 114 | 2650.84 | 152.5600 | 97.96 | 54.6000 | 1.338246 | 2.590909 | 23.252982 | 2.226364 | 3.467273 |
| 6 | 2024-11-07 | 42 | 188 | 1512.49 | 90.7798 | 55.38 | 35.3998 | 0.482871 | 4.476190 | 8.045160 | 1.318571 | 2.161424 |
| 7 | 2024-11-08 | 40 | 159 | 1579.53 | 99.5242 | 60.37 | 39.1542 | 0.625938 | 3.975000 | 9.934151 | 1.509250 | 2.488105 |
| 8 | 2024-11-09 | 25 | 75 | 794.79 | 52.6955 | 33.84 | 18.8555 | 0.702607 | 3.000000 | 10.597200 | 1.353600 | 2.107820 |
| 9 | 2024-11-10 | 18 | 52 | 318.63 | 22.0400 | 13.36 | 8.6800 | 0.423846 | 2.888889 | 6.127500 | 0.742222 | 1.224444 |
| 10 | 2024-11-11 | 111 | 566 | 8787.22 | 422.7193 | 261.78 | 160.9393 | 0.746854 | 5.099099 | 15.525124 | 2.358378 | 3.808282 |
| 11 | 2024-11-12 | 82 | 354 | 4526.46 | 278.7402 | 167.59 | 111.1502 | 0.787402 | 4.317073 | 12.786610 | 2.043780 | 3.399271 |
| 12 | 2024-11-13 | 51 | 233 | 2836.17 | 193.5732 | 120.81 | 72.7632 | 0.830786 | 4.568627 | 12.172403 | 2.368824 | 3.795553 |
| 13 | 2024-11-14 | 53 | 172 | 1544.83 | 107.6340 | 64.64 | 42.9940 | 0.625779 | 3.245283 | 8.981570 | 1.219623 | 2.030830 |
| 14 | 2024-11-15 | 60 | 170 | 2230.47 | 147.2729 | 90.80 | 56.4729 | 0.866311 | 2.833333 | 13.120412 | 1.513333 | 2.454548 |
| 15 | 2024-11-16 | 51 | 170 | 2051.63 | 140.4863 | 87.71 | 52.7763 | 0.826390 | 3.333333 | 12.068412 | 1.719804 | 2.754633 |
| 16 | 2024-11-17 | 62 | 229 | 2249.57 | 137.2043 | 85.42 | 51.7843 | 0.599145 | 3.693548 | 9.823450 | 1.377742 | 2.212973 |
| 17 | 2024-11-18 | 61 | 195 | 2330.68 | 154.8256 | 96.40 | 58.4256 | 0.793977 | 3.196721 | 11.952205 | 1.580328 | 2.538125 |
| 18 | 2024-11-19 | 42 | 169 | 3389.57 | 188.1850 | 118.02 | 70.1650 | 1.113521 | 4.023810 | 20.056627 | 2.810000 | 4.480595 |
| 19 | 2024-11-20 | 22 | 60 | 1304.38 | 91.3000 | 57.17 | 34.1300 | 1.521667 | 2.727273 | 21.739667 | 2.598636 | 4.150000 |
| 20 | 2024-11-21 | 23 | 42 | 627.64 | 41.7000 | 25.59 | 16.1100 | 0.992857 | 1.826087 | 14.943810 | 1.112609 | 1.813043 |
| 21 | 2024-11-22 | 55 | 182 | 2702.64 | 171.8356 | 100.45 | 71.3856 | 0.944152 | 3.309091 | 14.849670 | 1.826364 | 3.124284 |
| 22 | 2024-11-23 | 50 | 126 | 935.73 | 63.5326 | 38.90 | 24.6326 | 0.504227 | 2.520000 | 7.426429 | 0.778000 | 1.270652 |
| 23 | 2024-11-24 | 39 | 110 | 1088.50 | 65.2286 | 41.02 | 24.2086 | 0.592987 | 2.820513 | 9.895455 | 1.051795 | 1.672528 |
| 24 | 2024-11-25 | 52 | 177 | 1858.45 | 118.7942 | 75.69 | 43.1042 | 0.671154 | 3.403846 | 10.499718 | 1.455577 | 2.284504 |
| 25 | 2024-11-26 | 47 | 136 | 2288.33 | 147.6982 | 91.50 | 56.1982 | 1.086016 | 2.893617 | 16.825956 | 1.946809 | 3.142515 |
| 26 | 2024-11-27 | 50 | 170 | 1805.18 | 116.4543 | 71.65 | 44.8043 | 0.685025 | 3.400000 | 10.618706 | 1.433000 | 2.329086 |
| 27 | 2024-11-28 | 51 | 180 | 2729.20 | 182.9670 | 113.25 | 69.7170 | 1.016483 | 3.529412 | 15.162222 | 2.220588 | 3.587588 |
| 28 | 2024-11-29 | 57 | 176 | 2377.06 | 152.0656 | 94.64 | 57.4256 | 0.864009 | 3.087719 | 13.506023 | 1.660351 | 2.667818 |
| 29 | 2024-11-30 | 59 | 226 | 2976.12 | 160.1995 | 98.87 | 61.3295 | 0.708847 | 3.830508 | 13.168673 | 1.675763 | 2.715246 |
| 30 | 2024-12-01 | 41 | 121 | 1582.17 | 80.0971 | 48.15 | 31.9471 | 0.661960 | 2.951220 | 13.075785 | 1.174390 | 1.953588 |
| 31 | 2024-12-02 | 62 | 171 | 2966.18 | 184.8400 | 119.41 | 65.4300 | 1.080936 | 2.758065 | 17.346082 | 1.925968 | 2.981290 |
| 32 | 2024-12-03 | 66 | 316 | 3703.49 | 261.0138 | 154.60 | 106.4138 | 0.825993 | 4.787879 | 11.719905 | 2.342424 | 3.954755 |
| 33 | 2024-12-04 | 51 | 176 | 1525.15 | 102.0900 | 63.88 | 38.2100 | 0.580057 | 3.450980 | 8.665625 | 1.252549 | 2.001765 |
df_reactivated_grouped['avg_purchase_value'].mean()
0.8553155422693444
df_reactivated_grouped['avg_purchase_frequency_rate'].mean()
3.4451775939799845
df_reactivated_grouped['customer_value '].mean()
2.8796629356619583
У реактивированных неплохие показатели по среднему доходу с заказа и среднему количеству заказов на одного пользователя, однако по customer value, реактивированные чуть менее ценны в сравнении с новыми пользователями или с пользователями на этом оффере в целом. Возможно причина в низких суммах покупок, т.е. низком GMV этой группы и/или низком среднем уровне этой группы пользователей.
df_2023[(df_2023['order_date']>='2023-11-01') & (df_2023['order_date']<='2023-12-04')]['user_id'].nunique()
6596
df_2023['traffic_type'] = df_2023['traffic_type'].map({1: 'web', 2: 'extension', 3: 'mobile'})
df_2023['traffic_type'].value_counts()
traffic_type mobile 398060 extension 331256 web 73200 Name: count, dtype: int64
Также как и в 2024, в 2023 году больше всего трафика через мобильные, расширение на втором месте.
df_merged_2023[['month', '%_extension_orders']]
| month | %_extension_orders | |
|---|---|---|
| 0 | 1 | 41.021773 |
| 1 | 2 | 46.372393 |
| 2 | 3 | 46.986265 |
| 3 | 4 | 49.890100 |
| 4 | 5 | 48.698517 |
| 5 | 6 | 39.091226 |
| 6 | 7 | 40.235263 |
| 7 | 8 | 39.948159 |
| 8 | 9 | 36.002355 |
| 9 | 10 | 34.167870 |
| 10 | 11 | 33.407768 |
| 11 | 12 | 40.690440 |
df_merged_2023['%_extension_orders'].median()
40.4628515173149
sns.barplot(x=df_merged_2023['month'], y=df_merged_2023['%_extension_orders'])
plt.title('etxension traffic % out of total by month')
Text(0.5, 1.0, 'etxension traffic % out of total by month')
df_grouped_2023
| month | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | %_extension_orders | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 5877 | 67608 | 703485.01 | 44598.3318 | 29549.91 | 15048.4218 | 0.659661 | 11.503828 | 10.405352 | 5.028060 | 7.588622 | 41.021773 |
| 1 | 2 | 5470 | 65925 | 642077.07 | 41021.4449 | 27230.29 | 13791.1549 | 0.622244 | 12.052102 | 9.739508 | 4.978115 | 7.499350 | 46.372393 |
| 2 | 3 | 6076 | 79287 | 873952.73 | 55014.4512 | 36404.55 | 18609.9012 | 0.693865 | 13.049210 | 11.022648 | 5.991532 | 9.054386 | 46.986265 |
| 3 | 4 | 5454 | 65969 | 599583.39 | 39053.7410 | 25740.07 | 13313.6710 | 0.592001 | 12.095526 | 9.088866 | 4.719485 | 7.160569 | 49.890100 |
| 4 | 5 | 5429 | 67692 | 650864.42 | 42151.3327 | 27644.60 | 14506.7327 | 0.622693 | 12.468595 | 9.615086 | 5.092024 | 7.764106 | 48.698517 |
| 5 | 6 | 5294 | 60213 | 689243.34 | 43839.4580 | 28712.05 | 15127.4080 | 0.728073 | 11.373819 | 11.446753 | 5.423508 | 8.280971 | 39.091226 |
| 6 | 7 | 5195 | 59848 | 679347.16 | 44291.5128 | 29320.81 | 14970.7028 | 0.740067 | 11.520308 | 11.351209 | 5.644044 | 8.525796 | 40.235263 |
| 7 | 8 | 5241 | 65585 | 835783.68 | 52247.2596 | 34569.64 | 17677.6196 | 0.796634 | 12.513833 | 12.743519 | 6.596001 | 9.968949 | 39.948159 |
| 8 | 9 | 5290 | 62857 | 698229.50 | 45023.8921 | 29966.92 | 15056.9721 | 0.716291 | 11.882231 | 11.108222 | 5.664824 | 8.511133 | 36.002355 |
| 9 | 10 | 5171 | 60273 | 636366.69 | 41778.8720 | 27629.48 | 14149.3920 | 0.693161 | 11.655966 | 10.558072 | 5.343160 | 8.079457 | 34.167870 |
| 10 | 11 | 6396 | 98073 | 1129618.64 | 71880.9938 | 47051.59 | 24829.4038 | 0.732934 | 15.333490 | 11.518141 | 7.356409 | 11.238429 | 33.407768 |
| 11 | 12 | 4778 | 49186 | 580338.29 | 36738.4886 | 24276.44 | 12462.0486 | 0.746930 | 10.294265 | 11.798851 | 5.080879 | 7.689093 | 40.690440 |
data = [go.Scatter(x=df_grouped_2023['month'], y=df_grouped_2023['customer_value '], name='customer_value 2023'),
go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['customer_value '], name='customer_value 2024')]
layout = go.Layout(barmode='stack', title= 'Customer value by month 2023-2024 Aliexpress Int.', xaxis_title='month', yaxis_title='US dollars$')
fig = go.Figure(data=data, layout=layout)
fig.show()
data = [go.Scatter(x=df_grouped_2023['month'], y=df_grouped_2023['avg_purchase_value'], name='avg_purchase_value 2023'),
go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['avg_purchase_value'], name='avg_purchase_value 2024')]
layout = go.Layout(barmode='stack', title= 'avg_purchase_value by month 2023-2024 Aliexpress Int.', xaxis_title='month', yaxis_title='revenue $ per order')
fig = go.Figure(data=data, layout=layout)
fig.show()
data = [go.Scatter(x=df_grouped_2023['month'], y=df_grouped_2023['avg_purchase_frequency_rate'], name='purchase_frequency 2023'),
go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['avg_purchase_frequency_rate'], name='purchase_frequency 2024')]
layout = go.Layout(barmode='stack', title= 'purchase_frequency by month 2023-2024 Aliexpress Int.', xaxis_title='month', yaxis_title='orders_per_user')
fig = go.Figure(data=data, layout=layout)
fig.show()
data = [go.Scatter(x=df_grouped_2023['month'], y=df_grouped_2023['user_count'], name='user_count 2023'),
go.Scatter(x=df_grouped_2024['month'], y=df_grouped_2024['user_count'], name='user_count 2024')]
layout = go.Layout(barmode='stack', title= 'user_count by month 2023-2024', xaxis_title='month', yaxis_title='users_count')
fig = go.Figure(data=data, layout=layout)
fig.show()
Основные метрики за 2023-2024 год по офферу Aliexpress Int.
df_grouped_2023['customer_value '].mean()
8.180213752125367
df_grouped_2024['customer_value '].mean()
7.472812971837596
df_grouped_2023['avg_purchase_value'].mean()
0.7050777648273072
df_grouped_2024['avg_purchase_value'].mean()
0.792967914358269
df_grouped_2023['avg_purchase_frequency_rate'].mean()
11.96716650020908
df_grouped_2024['avg_purchase_frequency_rate'].mean()
9.801491461611164
df_grouped_2023['avg_purchase'].mean()
10.866352275766758
df_grouped_2024['avg_purchase'].mean()
12.579711707814027
df_grouped_2023['avg_user_cashback'].mean()
5.576503449349789
df_grouped_2024['avg_user_cashback'].mean()
4.967450845356287
df_grouped_2023['user_count'].mean()
5472.583333333333
df_grouped_2024['user_count'].mean()
3942.8333333333335
df_grouped_nov_2023
| order_date | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2023-11-01 | 592 | 2438 | 26216.49 | 1777.7440 | 1182.91 | 594.8340 | 0.729181 | 4.118243 | 10.753277 | 1.998159 | 3.002946 |
| 1 | 2023-11-02 | 559 | 2520 | 29252.59 | 1916.2716 | 1271.53 | 644.7416 | 0.760425 | 4.508050 | 11.608171 | 2.274651 | 3.428035 |
| 2 | 2023-11-03 | 519 | 2005 | 21005.58 | 1359.1410 | 890.05 | 469.0910 | 0.677876 | 3.863198 | 10.476599 | 1.714933 | 2.618769 |
| 3 | 2023-11-04 | 479 | 2048 | 22457.40 | 1525.0829 | 1012.39 | 512.6929 | 0.744669 | 4.275574 | 10.965527 | 2.113549 | 3.183889 |
| 4 | 2023-11-05 | 392 | 1303 | 11757.68 | 784.5350 | 520.74 | 263.7950 | 0.602099 | 3.323980 | 9.023546 | 1.328418 | 2.001365 |
| 5 | 2023-11-06 | 472 | 1901 | 21703.86 | 1472.8573 | 970.32 | 502.5373 | 0.774780 | 4.027542 | 11.417075 | 2.055763 | 3.120460 |
| 6 | 2023-11-07 | 470 | 1876 | 19884.34 | 1307.1045 | 870.12 | 436.9845 | 0.696751 | 3.991489 | 10.599328 | 1.851319 | 2.781073 |
| 7 | 2023-11-08 | 463 | 2002 | 20006.12 | 1317.4219 | 877.96 | 439.4619 | 0.658053 | 4.323974 | 9.993067 | 1.896242 | 2.845404 |
| 8 | 2023-11-09 | 397 | 2005 | 17117.96 | 1185.0003 | 750.85 | 434.1503 | 0.591023 | 5.050378 | 8.537636 | 1.891310 | 2.984887 |
| 9 | 2023-11-10 | 358 | 1494 | 16931.66 | 1199.8690 | 766.74 | 433.1290 | 0.803125 | 4.173184 | 11.333106 | 2.141732 | 3.351589 |
| 10 | 2023-11-11 | 1772 | 10694 | 138002.40 | 8401.7828 | 5344.34 | 3057.4428 | 0.785654 | 6.034989 | 12.904657 | 3.015993 | 4.741412 |
| 11 | 2023-11-12 | 1256 | 6746 | 72469.75 | 4490.0658 | 2906.16 | 1583.9058 | 0.665589 | 5.371019 | 10.742625 | 2.313822 | 3.574893 |
| 12 | 2023-11-13 | 1037 | 5390 | 50100.34 | 3191.4578 | 2121.31 | 1070.1478 | 0.592107 | 5.197686 | 9.295054 | 2.045622 | 3.077587 |
| 13 | 2023-11-14 | 904 | 4398 | 47084.96 | 2981.8632 | 1986.70 | 995.1632 | 0.678004 | 4.865044 | 10.705994 | 2.197677 | 3.298521 |
| 14 | 2023-11-15 | 933 | 4511 | 57992.25 | 3644.3850 | 2425.83 | 1218.5550 | 0.807888 | 4.834941 | 12.855742 | 2.600032 | 3.906093 |
| 15 | 2023-11-16 | 887 | 4013 | 59553.18 | 3705.2534 | 2444.11 | 1261.1434 | 0.923313 | 4.524239 | 14.840065 | 2.755479 | 4.177287 |
| 16 | 2023-11-17 | 956 | 4494 | 56309.40 | 3494.1469 | 2294.99 | 1199.1569 | 0.777514 | 4.700837 | 12.529907 | 2.400617 | 3.654965 |
| 17 | 2023-11-18 | 868 | 4383 | 48218.86 | 3145.2367 | 2092.52 | 1052.7167 | 0.717599 | 5.049539 | 11.001337 | 2.410737 | 3.623545 |
| 18 | 2023-11-19 | 449 | 1668 | 14858.15 | 913.1508 | 607.44 | 305.7108 | 0.547453 | 3.714922 | 8.907764 | 1.352873 | 2.033743 |
| 19 | 2023-11-20 | 442 | 1923 | 18274.51 | 1157.5725 | 766.17 | 391.4025 | 0.601962 | 4.350679 | 9.503125 | 1.733416 | 2.618942 |
| 20 | 2023-11-21 | 419 | 1702 | 14916.39 | 975.8979 | 642.85 | 333.0479 | 0.573383 | 4.062053 | 8.764036 | 1.534248 | 2.329112 |
| 21 | 2023-11-22 | 388 | 1397 | 15668.78 | 1103.4221 | 702.14 | 401.2821 | 0.789851 | 3.600515 | 11.216020 | 1.809639 | 2.843871 |
| 22 | 2023-11-23 | 857 | 3951 | 61900.30 | 3762.7734 | 2460.70 | 1302.0734 | 0.952360 | 4.610268 | 15.666996 | 2.871295 | 4.390634 |
| 23 | 2023-11-24 | 844 | 3618 | 49666.59 | 2968.2349 | 1942.93 | 1025.3049 | 0.820408 | 4.286730 | 13.727637 | 2.302050 | 3.516866 |
| 24 | 2023-11-25 | 703 | 2800 | 32140.58 | 2043.8839 | 1314.64 | 729.2439 | 0.729959 | 3.982930 | 11.478779 | 1.870043 | 2.907374 |
| 25 | 2023-11-26 | 748 | 2747 | 27327.43 | 1775.3034 | 1124.52 | 650.7834 | 0.646270 | 3.672460 | 9.948100 | 1.503369 | 2.373400 |
| 26 | 2023-11-27 | 739 | 3300 | 40838.19 | 2686.9941 | 1788.71 | 898.2841 | 0.814241 | 4.465494 | 12.375209 | 2.420447 | 3.635987 |
| 27 | 2023-11-28 | 704 | 3009 | 33251.68 | 2103.8334 | 1384.80 | 719.0334 | 0.699180 | 4.274148 | 11.050741 | 1.967045 | 2.988400 |
| 28 | 2023-11-29 | 905 | 3903 | 40426.15 | 2628.8506 | 1725.81 | 903.0406 | 0.673546 | 4.312707 | 10.357712 | 1.906972 | 2.904807 |
| 29 | 2023-11-30 | 904 | 3834 | 44285.07 | 2861.8577 | 1861.31 | 1000.5477 | 0.746442 | 4.241150 | 11.550618 | 2.058971 | 3.165772 |
| 30 | 2023-12-01 | 439 | 1575 | 19587.30 | 1281.7969 | 849.79 | 432.0069 | 0.813839 | 3.587699 | 12.436381 | 1.935740 | 2.919811 |
| 31 | 2023-12-02 | 406 | 1387 | 13021.53 | 849.4915 | 553.83 | 295.6615 | 0.612467 | 3.416256 | 9.388270 | 1.364113 | 2.092344 |
| 32 | 2023-12-03 | 391 | 1345 | 11118.85 | 733.8585 | 478.78 | 255.0785 | 0.545620 | 3.439898 | 8.266803 | 1.224501 | 1.876876 |
| 33 | 2023-12-04 | 457 | 1936 | 20871.47 | 1366.2389 | 907.95 | 458.2889 | 0.705702 | 4.236324 | 10.780718 | 1.986761 | 2.989582 |
Сравним метрики 2023 и 2024 отдельно за время Ноябрьских рапродаж
df_grouped_nov_2023['customer_value '].mean()
3.0870659634391338
df_grouped_nov_2024['customer_value '].mean()
3.25048700120043
df_grouped_nov_2023['avg_purchase_value'].mean()
0.7134803555602464
df_grouped_nov_2024['avg_purchase_value'].mean()
0.8245389216618724
df_grouped_nov_2023['avg_purchase_frequency_rate'].mean()
4.308474727048903
df_grouped_nov_2024['avg_purchase_frequency_rate'].mean()
3.9154669290678035
df_grouped_nov_2023['avg_purchase'].mean()
11.029459390786267
df_grouped_nov_2024['avg_purchase'].mean()
12.669058470814145
df_grouped_nov_2023['avg_user_cashback'].mean()
2.02492765808567
df_grouped_nov_2024['avg_user_cashback'].mean()
2.0993557764665214
df_grouped_nov_2023['user_count'].mean()
679.6764705882352
df_grouped_nov_2024['user_count'].mean()
547.6176470588235
df_2023[(df_2023['reg_date'] >= '2023-11-01') & (df_2023['reg_date'] <= '2023-12-04')]['user_id'].nunique()
267
267 / 6596 * 100
4.0479078229229835
df_new_users_nov_2023
| order_date | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2023-11-02 | 2 | 6 | 24.01 | 1.5900 | 0.80 | 0.7900 | 0.265000 | 3.000000 | 4.001667 | 0.400000 | 0.795000 |
| 1 | 2023-11-03 | 3 | 6 | 1035.64 | 52.7200 | 24.92 | 27.8000 | 8.786667 | 2.000000 | 172.606667 | 8.306667 | 17.573333 |
| 2 | 2023-11-04 | 2 | 3 | 163.82 | 11.4500 | 5.35 | 6.1000 | 3.816667 | 1.500000 | 54.606667 | 2.675000 | 5.725000 |
| 3 | 2023-11-05 | 3 | 7 | 41.42 | 2.9700 | 1.37 | 1.6000 | 0.424286 | 2.333333 | 5.917143 | 0.456667 | 0.990000 |
| 4 | 2023-11-06 | 4 | 30 | 113.97 | 7.7800 | 3.63 | 4.1500 | 0.259333 | 7.500000 | 3.799000 | 0.907500 | 1.945000 |
| 5 | 2023-11-07 | 9 | 133 | 264.03 | 16.6500 | 7.80 | 8.8500 | 0.125188 | 14.777778 | 1.985188 | 0.866667 | 1.850000 |
| 6 | 2023-11-08 | 8 | 60 | 244.97 | 17.0100 | 8.59 | 8.4200 | 0.283500 | 7.500000 | 4.082833 | 1.073750 | 2.126250 |
| 7 | 2023-11-09 | 9 | 169 | 237.49 | 16.2500 | 7.34 | 8.9100 | 0.096154 | 18.777778 | 1.405266 | 0.815556 | 1.805556 |
| 8 | 2023-11-10 | 7 | 33 | 208.12 | 13.8400 | 7.14 | 6.7000 | 0.419394 | 4.714286 | 6.306667 | 1.020000 | 1.977143 |
| 9 | 2023-11-11 | 42 | 271 | 1545.93 | 101.4000 | 48.18 | 53.2200 | 0.374170 | 6.452381 | 5.704539 | 1.147143 | 2.414286 |
| 10 | 2023-11-12 | 30 | 248 | 788.65 | 52.2536 | 24.44 | 27.8136 | 0.210700 | 8.266667 | 3.180040 | 0.814667 | 1.741787 |
| 11 | 2023-11-13 | 24 | 236 | 619.56 | 44.1305 | 19.39 | 24.7405 | 0.186994 | 9.833333 | 2.625254 | 0.807917 | 1.838771 |
| 12 | 2023-11-14 | 20 | 230 | 1180.46 | 80.3700 | 40.63 | 39.7400 | 0.349435 | 11.500000 | 5.132435 | 2.031500 | 4.018500 |
| 13 | 2023-11-15 | 30 | 287 | 830.85 | 48.4500 | 24.20 | 24.2500 | 0.168815 | 9.566667 | 2.894948 | 0.806667 | 1.615000 |
| 14 | 2023-11-16 | 20 | 265 | 1108.26 | 73.9000 | 34.68 | 39.2200 | 0.278868 | 13.250000 | 4.182113 | 1.734000 | 3.695000 |
| 15 | 2023-11-17 | 17 | 237 | 1085.12 | 60.7800 | 29.11 | 31.6700 | 0.256456 | 13.941176 | 4.578565 | 1.712353 | 3.575294 |
| 16 | 2023-11-18 | 16 | 253 | 402.05 | 25.9200 | 12.25 | 13.6700 | 0.102451 | 15.812500 | 1.589130 | 0.765625 | 1.620000 |
| 17 | 2023-11-19 | 7 | 186 | 124.78 | 7.9900 | 2.60 | 5.3900 | 0.042957 | 26.571429 | 0.670860 | 0.371429 | 1.141429 |
| 18 | 2023-11-20 | 12 | 211 | 437.31 | 29.6200 | 12.64 | 16.9800 | 0.140379 | 17.583333 | 2.072559 | 1.053333 | 2.468333 |
| 19 | 2023-11-21 | 9 | 192 | 349.07 | 22.7994 | 9.95 | 12.8494 | 0.118747 | 21.333333 | 1.818073 | 1.105556 | 2.533267 |
| 20 | 2023-11-22 | 9 | 27 | 237.57 | 17.3100 | 8.39 | 8.9200 | 0.641111 | 3.000000 | 8.798889 | 0.932222 | 1.923333 |
| 21 | 2023-11-23 | 24 | 80 | 972.17 | 67.5200 | 32.10 | 35.4200 | 0.844000 | 3.333333 | 12.152125 | 1.337500 | 2.813333 |
| 22 | 2023-11-24 | 24 | 94 | 776.45 | 50.7336 | 25.08 | 25.6536 | 0.539719 | 3.916667 | 8.260106 | 1.045000 | 2.113900 |
| 23 | 2023-11-25 | 15 | 33 | 167.69 | 11.5700 | 5.35 | 6.2200 | 0.350606 | 2.200000 | 5.081515 | 0.356667 | 0.771333 |
| 24 | 2023-11-26 | 23 | 68 | 566.73 | 35.7400 | 17.47 | 18.2700 | 0.525588 | 2.956522 | 8.334265 | 0.759565 | 1.553913 |
| 25 | 2023-11-27 | 12 | 30 | 203.23 | 14.4200 | 6.86 | 7.5600 | 0.480667 | 2.500000 | 6.774333 | 0.571667 | 1.201667 |
| 26 | 2023-11-28 | 11 | 84 | 1119.82 | 74.1200 | 37.98 | 36.1400 | 0.882381 | 7.636364 | 13.331190 | 3.452727 | 6.738182 |
| 27 | 2023-11-29 | 16 | 156 | 665.56 | 45.9900 | 23.11 | 22.8800 | 0.294808 | 9.750000 | 4.266410 | 1.444375 | 2.874375 |
| 28 | 2023-11-30 | 20 | 109 | 889.37 | 60.2900 | 30.42 | 29.8700 | 0.553119 | 5.450000 | 8.159358 | 1.521000 | 3.014500 |
| 29 | 2023-12-01 | 17 | 41 | 453.95 | 31.6600 | 16.11 | 15.5500 | 0.772195 | 2.411765 | 11.071951 | 0.947647 | 1.862353 |
| 30 | 2023-12-02 | 11 | 127 | 419.07 | 26.7700 | 12.71 | 14.0600 | 0.210787 | 11.545455 | 3.299764 | 1.155455 | 2.433636 |
| 31 | 2023-12-03 | 10 | 153 | 374.51 | 25.3300 | 12.65 | 12.6800 | 0.165556 | 15.300000 | 2.447778 | 1.265000 | 2.533000 |
| 32 | 2023-12-04 | 11 | 57 | 338.62 | 19.5100 | 10.38 | 9.1300 | 0.342281 | 5.181818 | 5.940702 | 0.943636 | 1.773636 |
df_new_users_nov_2023['customer_value '].mean()
2.8198821165505774
df_new_users_nov_2023['avg_purchase_value'].mean()
0.7063326295389718
df_new_users_nov_2023['avg_purchase_frequency_rate'].mean()
8.830179295138027
В 2023 году в оффере Aliexpress Int. новые пользователи по показателям не уступают общим показателям по пользователям, а по количеству заказов на одного пользователя, новые превосходят почти в два раза.
df_2023[df_2023['cashback_link_count'] >= 50]['user_id'].drop_duplicates()
136 118119
140 2698085
142 1462568
171 32558
303 25794
...
720283 1058008
728090 126200
728880 3061279
764875 191729
790718 1948844
Name: user_id, Length: 213, dtype: int64
Всего 213 крупных создателей кэшбек ссылок, более 50 ссылок
df_2023[df_2023['cashback_link_count'].notna()]['user_id'].nunique()
3622
Всего создателей кб ссылок на оффере Aliexpress Int.- 3622
df_2023[(df_2023['cashback_link_count'].notna()) & (df_2023['order_date']>= '2023-11-01')
& (df_2023['order_date']<= '2023-12-04')]['user_id'].nunique()
2004
2004 / 3622 * 100
55.32854776366648
df_grouped_cb_link_2023 = df_2023[df_2023['cashback_link_count'].notna()].groupby(['month']).agg(user_count = ('user_id', 'nunique'), order_count = ('order_number', 'nunique'),
GMV_sum = ('GMV', 'sum'), revenue_sum = ('Revenue', 'sum'),
user_com_sum = ('users_comission', 'sum')).reset_index()
df_grouped_cb_link_2023
| month | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | |
|---|---|---|---|---|---|---|
| 0 | 1 | 1825 | 24630 | 269435.84 | 16750.1267 | 11201.22 |
| 1 | 2 | 1678 | 24097 | 252058.20 | 15544.1622 | 10396.27 |
| 2 | 3 | 1889 | 28799 | 311380.81 | 19079.2014 | 12704.55 |
| 3 | 4 | 1696 | 26607 | 233427.03 | 15142.1985 | 10103.48 |
| 4 | 5 | 1726 | 28864 | 268424.31 | 17121.4943 | 11286.16 |
| 5 | 6 | 1650 | 24047 | 289006.62 | 18242.3530 | 11899.79 |
| 6 | 7 | 1625 | 26190 | 303794.82 | 19714.5188 | 13110.60 |
| 7 | 8 | 1658 | 30433 | 387665.44 | 23739.9515 | 15943.00 |
| 8 | 9 | 1665 | 28331 | 325270.97 | 20669.6680 | 14022.45 |
| 9 | 10 | 1601 | 26682 | 282555.45 | 18627.2193 | 12555.74 |
| 10 | 11 | 1960 | 46767 | 497745.80 | 31265.4596 | 20842.87 |
| 11 | 12 | 1495 | 21896 | 251114.23 | 16050.4259 | 10827.01 |
df_grouped_cb_link_2023['revenue_sum'].sum() / df_grouped_2023['revenue_sum'].sum() * 100
41.594374745631605
df_reactivated_2023
| id | date | user_id | order_number | order_status | GMV | Revenue | offer_id | traffic_type | country | user_status | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1207611460 | 2023-01-01 | 2175790 | 5095020934464829 | completed | 16.37 | 0.6136 | 28728 | 2 | None | reactivated |
| 1 | 1207611462 | 2023-01-01 | 2175790 | 5095020934424829 | completed | 4.34 | 0.0975 | 28728 | 2 | None | reactivated |
| 2 | 1207611463 | 2023-01-01 | 2175790 | 5095020934404829 | completed | 53.66 | 3.0660 | 28728 | 2 | None | reactivated |
| 3 | 1207611464 | 2023-01-01 | 2175790 | 5095020934394829 | completed | 39.64 | 2.2651 | 28728 | 2 | None | reactivated |
| 4 | 1207611592 | 2023-01-01 | 800601 | 8160713511228104 | completed | 6.26 | 0.4000 | 463 | 3 | None | reactivated |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 170384 | 1210440694 | 2023-12-31 | 526730 | 5293804439164071 | completed | 1.76 | 0.1337 | 28728 | 3 | None | reactivated |
| 170385 | 1210445021 | 2023-12-31 | 2000822 | 5294102661345862 | completed | 9.50 | 0.2715 | 28728 | 3 | None | reactivated |
| 170386 | 1210587184 | 2023-12-31 | 71461 | 20411622640_c | rejected | 0.09 | 0.0100 | 797 | 1 | None | reactivated |
| 170387 | 1210587207 | 2023-12-31 | 288802 | 20411645516_c | completed | 3.66 | 0.3662 | 797 | 1 | None | reactivated |
| 170388 | 1210587208 | 2023-12-31 | 288802 | 20411614202_c | completed | 0.10 | 0.0100 | 797 | 1 | None | reactivated |
170389 rows × 11 columns
df_2023_nov = df_2023[(df_2023['order_date']>='2023-11-01') & (df_2023['order_date']<='2023-12-04')]
df_2023_nov[df_2023_nov['user_id'].isin(df_reactivated_2023['user_id'])]['user_id'].nunique()
1002
Посмотрим активность реактивированных по тем же метрикам в течении периода распродаж.
df_reactivated_grouped_2023
| order_date | user_count | order_count | GMV_sum | revenue_sum | user_com_sum | gross_profit | avg_purchase_value | avg_purchase_frequency_rate | avg_purchase | avg_user_cashback | customer_value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2023-11-01 | 61 | 205 | 2644.46 | 183.3800 | 122.50 | 60.8800 | 0.894537 | 3.360656 | 12.899805 | 2.008197 | 3.006230 |
| 1 | 2023-11-02 | 48 | 160 | 1331.90 | 91.5300 | 59.64 | 31.8900 | 0.572063 | 3.333333 | 8.324375 | 1.242500 | 1.906875 |
| 2 | 2023-11-03 | 51 | 180 | 2565.07 | 134.9600 | 92.17 | 42.7900 | 0.749778 | 3.529412 | 14.250389 | 1.807255 | 2.646275 |
| 3 | 2023-11-04 | 54 | 185 | 1786.95 | 122.8338 | 79.24 | 43.5938 | 0.663966 | 3.425926 | 9.659189 | 1.467407 | 2.274700 |
| 4 | 2023-11-05 | 51 | 118 | 825.77 | 56.3938 | 36.56 | 19.8338 | 0.477914 | 2.313725 | 6.998051 | 0.716863 | 1.105761 |
| 5 | 2023-11-06 | 47 | 173 | 2137.47 | 139.0487 | 91.18 | 47.8687 | 0.803750 | 3.680851 | 12.355318 | 1.940000 | 2.958483 |
| 6 | 2023-11-07 | 55 | 157 | 3318.89 | 180.6200 | 120.15 | 60.4700 | 1.150446 | 2.854545 | 21.139427 | 2.184545 | 3.284000 |
| 7 | 2023-11-08 | 49 | 154 | 1716.36 | 109.9502 | 72.16 | 37.7902 | 0.713962 | 3.142857 | 11.145195 | 1.472653 | 2.243882 |
| 8 | 2023-11-09 | 46 | 148 | 1212.34 | 82.7000 | 56.26 | 26.4400 | 0.558784 | 3.217391 | 8.191486 | 1.223043 | 1.797826 |
| 9 | 2023-11-10 | 41 | 125 | 1903.40 | 133.0600 | 89.06 | 44.0000 | 1.064480 | 3.048780 | 15.227200 | 2.172195 | 3.245366 |
| 10 | 2023-11-11 | 240 | 1012 | 12648.86 | 766.6152 | 469.45 | 297.1652 | 0.757525 | 4.216667 | 12.498874 | 1.956042 | 3.194230 |
| 11 | 2023-11-12 | 132 | 519 | 5070.96 | 326.9062 | 204.16 | 122.7462 | 0.629877 | 3.931818 | 9.770636 | 1.546667 | 2.476562 |
| 12 | 2023-11-13 | 112 | 442 | 3064.49 | 209.9300 | 137.80 | 72.1300 | 0.474955 | 3.946429 | 6.933235 | 1.230357 | 1.874375 |
| 13 | 2023-11-14 | 106 | 381 | 3718.59 | 251.6549 | 156.54 | 95.1149 | 0.660512 | 3.594340 | 9.760079 | 1.476792 | 2.374103 |
| 14 | 2023-11-15 | 99 | 308 | 4941.16 | 324.5100 | 210.99 | 113.5200 | 1.053604 | 3.111111 | 16.042727 | 2.131212 | 3.277879 |
| 15 | 2023-11-16 | 101 | 332 | 3723.07 | 214.2185 | 140.74 | 73.4785 | 0.645236 | 3.287129 | 11.214066 | 1.393465 | 2.120975 |
| 16 | 2023-11-17 | 118 | 434 | 5437.32 | 353.7098 | 227.94 | 125.7698 | 0.815000 | 3.677966 | 12.528387 | 1.931695 | 2.997541 |
| 17 | 2023-11-18 | 95 | 439 | 3608.13 | 238.3500 | 155.18 | 83.1700 | 0.542938 | 4.621053 | 8.218975 | 1.633474 | 2.508947 |
| 18 | 2023-11-19 | 58 | 173 | 1014.28 | 69.5028 | 44.41 | 25.0928 | 0.401750 | 2.982759 | 5.862890 | 0.765690 | 1.198324 |
| 19 | 2023-11-20 | 59 | 171 | 2371.92 | 163.6500 | 110.48 | 53.1700 | 0.957018 | 2.898305 | 13.870877 | 1.872542 | 2.773729 |
| 20 | 2023-11-21 | 56 | 228 | 2219.16 | 152.7000 | 100.55 | 52.1500 | 0.669737 | 4.071429 | 9.733158 | 1.795536 | 2.726786 |
| 21 | 2023-11-22 | 43 | 183 | 1987.99 | 135.9400 | 91.18 | 44.7600 | 0.742842 | 4.255814 | 10.863333 | 2.120465 | 3.161395 |
| 22 | 2023-11-23 | 104 | 447 | 8146.40 | 437.6089 | 281.45 | 156.1589 | 0.978991 | 4.298077 | 18.224609 | 2.706250 | 4.207778 |
| 23 | 2023-11-24 | 97 | 314 | 4811.08 | 281.9800 | 186.04 | 95.9400 | 0.898025 | 3.237113 | 15.321911 | 1.917938 | 2.907010 |
| 24 | 2023-11-25 | 76 | 322 | 3027.45 | 197.2920 | 124.19 | 73.1020 | 0.612708 | 4.236842 | 9.402019 | 1.634079 | 2.595947 |
| 25 | 2023-11-26 | 82 | 290 | 2523.14 | 169.0146 | 107.71 | 61.3046 | 0.582809 | 3.536585 | 8.700483 | 1.313537 | 2.061154 |
| 26 | 2023-11-27 | 85 | 288 | 2647.78 | 174.6477 | 111.88 | 62.7677 | 0.606416 | 3.388235 | 9.193681 | 1.316235 | 2.054679 |
| 27 | 2023-11-28 | 88 | 381 | 5194.97 | 292.2500 | 193.83 | 98.4200 | 0.767060 | 4.329545 | 13.635092 | 2.202614 | 3.321023 |
| 28 | 2023-11-29 | 99 | 416 | 4117.39 | 281.5628 | 184.19 | 97.3728 | 0.676834 | 4.202020 | 9.897572 | 1.860505 | 2.844069 |
| 29 | 2023-11-30 | 105 | 341 | 4171.96 | 236.0200 | 149.98 | 86.0400 | 0.692141 | 3.247619 | 12.234487 | 1.428381 | 2.247810 |
| 30 | 2023-12-01 | 54 | 163 | 1195.90 | 82.7727 | 52.06 | 30.7127 | 0.507808 | 3.018519 | 7.336810 | 0.964074 | 1.532828 |
| 31 | 2023-12-02 | 37 | 92 | 1264.33 | 85.1200 | 53.40 | 31.7200 | 0.925217 | 2.486486 | 13.742717 | 1.443243 | 2.300541 |
| 32 | 2023-12-03 | 50 | 175 | 1539.47 | 86.9674 | 56.29 | 30.6774 | 0.496957 | 3.500000 | 8.796971 | 1.125800 | 1.739348 |
| 33 | 2023-12-04 | 43 | 265 | 2012.65 | 137.6900 | 91.37 | 46.3200 | 0.519585 | 6.162791 | 7.594906 | 2.124884 | 3.202093 |
df_reactivated_grouped_2023['avg_purchase_value'].mean()
0.7136830015944523
df_reactivated_grouped_2023['avg_purchase_frequency_rate'].mean()
3.5925332068427105
df_reactivated_grouped_2023['customer_value '].mean()
2.5343682566475447